package AccessDB;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import model.Bimestre;
import model.Boletim;
import model.Disciplina;

public class BoletimDAO {
	
	Connection con;
	
	public BoletimDAO() {
		
		con = ConnectionDB.getInstance().getConnection();
		
	}
	
	public List<Boletim> retornaBoletimDoAluno(String matAluno, int idDisciplina, int ano) throws SQLException{
		
		List<Integer> idsDisciplinaProf = new DisciplinaDAO().retornaIdsDisciplinaProf(idDisciplina);
		List<Bimestre> bimestres = new BimestreDAO().retornaBimestresDoAno(ano);
		List<Boletim> boletins = new ArrayList<Boletim>();
		
		Disciplina disciplina = new DisciplinaDAO().retornaDisciplina(idDisciplina);
		
		for (Bimestre bim : bimestres){
			Boletim boletim = new Boletim();
			float nota = retornaNotaDoAlunoEmDisciplina(matAluno, idsDisciplinaProf, bim.getIdBimestre());
			boletim.setNota(nota);
			boletim.setNumBimestre(bim.getNumeroBimestre());
			boletim.setDisciplina(disciplina);
			boletins.add(boletim);
		}
		
		return boletins;		
	}
	
	public float retornaNotaDoAlunoEmDisciplina(String matAluno, List<Integer> idsDisciplinaProf, int idBimestre) throws SQLException{
		
		PreparedStatement stmt = con.prepareStatement("select peso, nota from AvaliacaoNota inner join " +
				"Avaliacao on Avaliacao.numAv = AvaliacaoNota.numAv where matAluno = ? and " +
				"idDisciplinaProf = ? and idBimestre = ?");
		
		float somaNota = 0;
		int somaPesos = 0;
		ResultSet rs;
		
		for (Integer id : idsDisciplinaProf){
			stmt.setString(1, matAluno);
			stmt.setInt(2, id);
			stmt.setInt(3, idBimestre);
			rs = stmt.executeQuery();
			while(rs.next()){
				somaNota += rs.getFloat("nota") * rs.getInt("peso");
				somaPesos += rs.getInt("peso");
			}
		}
				
		return somaNota/somaPesos;
	}
	

	
}